package com.magenta.maxoptra.gis.distancematrix;

import com.magenta.maxoptra.cache.db.Dialect;
import com.magenta.maxoptra.cache.db.Util;
import com.magenta.maxoptra.commons.db.DataSourceQueryExecuter;
import com.magenta.maxoptra.commons.db.QueryExecuter;
import com.magenta.maxoptra.gis.GeographicCoordinates;
import com.magenta.maxoptra.gis.commons.Logger;
import com.magenta.maxoptra.gis.commons.Pair;
import com.magenta.maxoptra.gis.datasources.GisDataSource;
import com.magenta.maxoptra.gis.datasources.GisTable;
import com.magenta.maxoptra.gis.utils.DatabaseUtils;

import javax.naming.NamingException;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * @author Sergey Grachev
 */
final class DistanceMatrixDAOImpl implements DistanceMatrixDAO {
    private static final Pair<Set<GeographicCoordinates>, Set<GeographicCoordinates>> EMPTY_MATRIX_COORDINATES =
            new Pair<Set<GeographicCoordinates>, Set<GeographicCoordinates>>(Collections.<GeographicCoordinates>emptySet(), Collections.<GeographicCoordinates>emptySet());
    private static final Pair<Set<DistanceMatrixCoordinate>, Set<DistanceMatrixCoordinate>> EMPTY_MATRIX_COORDINATES2 =
            new Pair<Set<DistanceMatrixCoordinate>, Set<DistanceMatrixCoordinate>>(Collections.<DistanceMatrixCoordinate>emptySet(), Collections.<DistanceMatrixCoordinate>emptySet());

    private static final DatabaseUtils.RowMapper<Object[], Long[]> MAPPER_GROUPS_FOR_UPDATE =
            new DatabaseUtils.RowMapper<Object[], Long[]>() {
                @Override
                public Long[] map(final Object[] row) {
                    final Long priority = (Long) row[2];
                    return new Long[]{(Long) row[0], (Long) row[1], priority != null ? priority : 0};
                }
            };

    private static final DatabaseUtils.RowGroupMapper<Object[], Long, GeographicCoordinates> MAPPER_GROUPS_COORDINATES =
            new DatabaseUtils.RowGroupMapper<Object[], Long, GeographicCoordinates>() {
                @Override
                public GeographicCoordinates map(final Object[] row) {
                    final Double latitude = ((BigDecimal) row[1]).doubleValue();
                    final Double longitude = ((BigDecimal) row[2]).doubleValue();
                    final Integer latitudeInt = (Integer) row[3];
                    final Integer longitudeInt = (Integer) row[4];
                    return new DistanceMatrixCoordinate(latitude, longitude, latitudeInt, longitudeInt);
                }

                @Override
                public Long group(final Object[] row) {
                    return (Long) row[0];
                }
            };

    private final Logger logger = new Logger(DistanceMatrixDAO.class);
    private final Map<Query, String> queries = new HashMap<Query, String>(5);
    private final GisDataSource gisDataSource;
    private QueryExecuter queryExecuter;
    private Dialect dialect;
    private String schemaName;

    public DistanceMatrixDAOImpl(final GisDataSource gisDataSource) throws NamingException, SQLException {
        this.gisDataSource = gisDataSource;
        if (gisDataSource.isNull()) {
            throw new IllegalArgumentException("Empty data source");
        }

        final DataSource ds = gisDataSource.lookup();
        if (ds != null) {
            queryExecuter = new DataSourceQueryExecuter(ds);
            final Connection connection = ds.getConnection();
            try {
                dialect = Util.selectDialect(connection.getMetaData());
                schemaName = connection.getCatalog();
                updateQueries();
            } finally {
                connection.close();
            }
        }
    }

    private void updateQueries() {
        final GisTable tblQueue = gisDataSource.table("queue");

        queries.put(Query.INSERT_COORDINATE, "INSERT INTO " + tblQueue.name() + " ("
                + tblQueue.column("groupId").name()
                + "," + tblQueue.column("matrixId").name()
                + "," + tblQueue.column("latitude").name()
                + "," + tblQueue.column("longitude").name()
                + "," + tblQueue.column("latitudeInt").name()
                + "," + tblQueue.column("longitudeInt").name()
                + "," + tblQueue.column("forwardState").name()
                + "," + tblQueue.column("backwardState").name()
                + "," + tblQueue.column("lastGeoProviderId").name()
                + ") VALUES (?,?,?,?,?,?,?,?,?)"
        );

        queries.put(Query.SELECT_EXISTS_COORDINATES_OF_GROUP, "SELECT "
                + tblQueue.column("matrixId").name()
                + "," + tblQueue.column("latitude").name()
                + "," + tblQueue.column("longitude").name()
                + "," + tblQueue.column("latitudeInt").name()
                + "," + tblQueue.column("longitudeInt").name()
                + " FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=?");

        queries.put(Query.SELECT_EXISTS_COORDINATES_OF_MATRIX_WITH_STATE, "SELECT "
                + tblQueue.column("latitude").name()
                + "," + tblQueue.column("longitude").name()
                + "," + tblQueue.column("latitudeInt").name()
                + "," + tblQueue.column("longitudeInt").name()
                + "," + tblQueue.column("forwardState").name()
                + "," + tblQueue.column("backwardState").name()
                + " FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=? AND " + tblQueue.column("matrixId").name() + "=?");

        queries.put(Query.SELECT_GROUPS_FOR_UPDATE, "SELECT "
                + tblQueue.column("groupId").name() + "," + tblQueue.column("matrixId").name() + "," + tblQueue.column("priority").name()
                + " FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("forwardState").name() + "=" + UpdatingState.SCHEDULED.getValue()
                + " OR " + tblQueue.column("backwardState").name() + "=" + UpdatingState.SCHEDULED.getValue()
                + " GROUP BY " + tblQueue.column("groupId").name() + "," + tblQueue.column("matrixId").name()
                + " ORDER BY " + tblQueue.column("priority").name() + " ASC"
        );

        queries.put(Query.REMOVE_GROUP, "DELETE FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=?");

        queries.put(Query.REMOVE_MATRICES_IN, "DELETE FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=? AND " + tblQueue.column("matrixId").name() + " in ");

        queries.put(Query.REMOVE_COORDINATE, "DELETE FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=?"
                + " AND " + tblQueue.column("matrixId").name() + "=?"
                + " AND " + tblQueue.column("latitude").name() + "=?"
                + " AND " + tblQueue.column("longitude").name() + "=?");

        queries.put(Query.COUNT_GROUP_COORDINATES_WITH_NOT_THIS_CID, "SELECT COUNT(*) FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=?"
                + " AND " + tblQueue.column("lastGeoProviderId").name() + "!=? AND " + tblQueue.column("lastGeoProviderId").name() + "!=-1");

        queries.put(Query.COUNT_COORDINATES_FOR_UPDATE, "SELECT COUNT(*) FROM " + tblQueue.name()
                + " WHERE " + tblQueue.column("groupId").name() + "=? AND " + tblQueue.column("matrixId").name() + "=?"
                + " AND (" + tblQueue.column("forwardState").name() + "<>0 OR " + tblQueue.column("backwardState").name() + "<>0)"
        );

        queries.put(Query.UPDATE_STATE_FOR_COORDINATES_OF_GROUP, "UPDATE " + tblQueue.name()
                + " SET " + tblQueue.column("forwardState").name() + "=?," + tblQueue.column("backwardState").name() + "=?," + tblQueue.column("lastGeoProviderId").name() + "=?"
                + " WHERE " + tblQueue.column("groupId").name() + "=?");

        queries.put(Query.UPDATE_FORWARD_STATE_FOR_COORDINATE, "UPDATE " + tblQueue.name()
                + " SET " + tblQueue.column("forwardState").name() + "=?," + tblQueue.column("lastGeoProviderId").name() + "=?"
                + " WHERE " + tblQueue.column("groupId").name() + "=? AND " + tblQueue.column("matrixId").name() + "=?"
                + " AND " + tblQueue.column("latitude").name() + "=? AND " + tblQueue.column("longitude").name() + "=?");

        queries.put(Query.UPDATE_BACKWARD_STATE_FOR_COORDINATE, "UPDATE " + tblQueue.name()
                + " SET " + tblQueue.column("backwardState").name() + "=?," + tblQueue.column("lastGeoProviderId").name() + "=?"
                + " WHERE " + tblQueue.column("groupId").name() + "=? AND " + tblQueue.column("matrixId").name() + "=?"
                + " AND " + tblQueue.column("latitude").name() + "=? AND " + tblQueue.column("longitude").name() + "=?");

        queries.put(Query.RESET_STATES_OF_GROUP, "UPDATE " + tblQueue.name()
                + " SET " + tblQueue.column("forwardState").name() + "=?," + tblQueue.column("backwardState").name() + "=?"
                + " WHERE " + tblQueue.column("groupId").name() + "=?");

        queries.put(Query.SET_PRIORITY, "UPDATE " + tblQueue.name()
                + " SET " + tblQueue.column("priority").name() + "=?"
                + " WHERE " + tblQueue.column("groupId").name() + "=? AND " + tblQueue.column("matrixId").name() + "=?");
    }

    private Map<Long, Set<GeographicCoordinates>> getExistsCoordinates(final Long groupId) {
        final List groupCoordinates;
        try {
            groupCoordinates = queryExecuter.getResultList(queries.get(Query.SELECT_EXISTS_COORDINATES_OF_GROUP),
                    DatabaseUtils.makeQueryParams(groupId));
        } catch (SQLException e) {
            logger.warn("Can't get coordinates for group " + groupId, e);
            return Collections.emptyMap();
        }

        return DatabaseUtils.mapGroupRowsAsSet(groupCoordinates, MAPPER_GROUPS_COORDINATES);
    }

    private boolean insertNewCoordinates(final long groupId, final long matrixId, final Set<GeographicCoordinates> coordinates) {
        boolean hasInserted = false;
        final Map<Integer, Object> params = DatabaseUtils.makeQueryParams(groupId, matrixId);
        for (final GeographicCoordinates coordinate : coordinates) {
            try {
                params.put(3, coordinate.getLatitude());
                params.put(4, coordinate.getLongitude());
                params.put(5, coordinate.getLatitudeInteger());
                params.put(6, coordinate.getLongitudeInteger());
                params.put(7, UpdatingState.SCHEDULED.getValue());
                params.put(8, UpdatingState.SCHEDULED.getValue());
                params.put(9, -1);
                hasInserted |= queryExecuter.executeUpdate(queries.get(Query.INSERT_COORDINATE), params) > 0;
            } catch (SQLException e) {
                logger.warn("Can't insert new coordinate " + coordinate, e);
            }
        }
        return hasInserted;
    }

    @Override
    public List<Long[]> getGroupsForUpdating() {
        final List groups;
        try {
            groups = queryExecuter.getResultList(queries.get(Query.SELECT_GROUPS_FOR_UPDATE));
        } catch (SQLException e) {
            logger.warn("Can't get groups for updating", e);
            return Collections.emptyList();
        }
        return DatabaseUtils.mapRows(groups, MAPPER_GROUPS_FOR_UPDATE);
    }

    @Override
    public boolean removeGroup(final long groupId) {
        try {
            return queryExecuter.executeUpdate(queries.get(Query.REMOVE_GROUP), DatabaseUtils.makeQueryParams(groupId)) > 0;
        } catch (SQLException e) {
            logger.warn("Can't remove group " + groupId, e);
        }
        return false;
    }

    @Override
    public boolean removeMatrices(final long groupId, final Set<Long> removedMatrices) {
        final Map<Integer, Object> params = DatabaseUtils.makeQueryParams(groupId);
        final String in = DatabaseUtils.inStatement(params, removedMatrices);
        try {
            return queryExecuter.executeUpdate(queries.get(Query.REMOVE_MATRICES_IN) + " " + in, params) > 0;
        } catch (SQLException e) {
            logger.warn("Can't remove group " + groupId, e);
        }
        return false;
    }

    @Override
    public boolean resetStatesOfGroup(final long groupId) {
        final Map<Integer, Object> params = DatabaseUtils.makeQueryParams(
                UpdatingState.SCHEDULED.getValue(), UpdatingState.SCHEDULED.getValue(), groupId);
        try {
            return queryExecuter.executeUpdate(queries.get(Query.RESET_STATES_OF_GROUP), params) > 0;
        } catch (SQLException e) {
            logger.warn("Can't reset states of coordinates of group " + groupId, e);
        }

        return false;
    }

    @Override
    public boolean isMatrixFullyCalculated(final long groupId, final long matrixId) {
        final Map<Integer, Object> params = DatabaseUtils.makeQueryParams(groupId, matrixId);
        try {
            final List countList = queryExecuter.getResultList(queries.get(Query.COUNT_COORDINATES_FOR_UPDATE), params);
            return ((Long) ((Object[]) countList.get(0))[0]) == 0;
        } catch (SQLException e) {
            logger.warn("Can't get count of coordinates scheduled for updating", e);
        }
        return false;
    }

    @Override
    public boolean setMatrixPriority(final long groupId, final long matrixId, final long priority) {
        final Map<Integer, Object> params = DatabaseUtils.makeQueryParams(priority, groupId, matrixId);
        try {
            return queryExecuter.executeUpdate(queries.get(Query.SET_PRIORITY), params) > 0;
        } catch (SQLException e) {
            logger.warn("Can't set priority for coordinates of group " + groupId + ", matrix " + matrixId, e);
        }
        return false;
    }

    @Override
    public boolean updateStateForCoordinatesForNewCid(final long groupId, final long newCid, final int state) {
        Map<Integer, Object> params = DatabaseUtils.makeQueryParams(groupId, newCid);
        final List countList;
        try {
            countList = queryExecuter.getResultList(queries.get(Query.COUNT_GROUP_COORDINATES_WITH_NOT_THIS_CID), params);
        } catch (SQLException e) {
            logger.warn("Can't get count of coordinates with different CID that " + newCid, e);
            return false;
        }

        final Long count = (Long) ((Object[]) countList.get(0))[0];
        if (count > 0) {
            params = DatabaseUtils.makeQueryParams(UpdatingState.SCHEDULED.getValue(), UpdatingState.SCHEDULED.getValue(), newCid, groupId);
            try {
                return queryExecuter.executeUpdate(queries.get(Query.UPDATE_STATE_FOR_COORDINATES_OF_GROUP), params) > 0;
            } catch (SQLException e) {
                logger.warn("Can't state of coordinates for group " + groupId, e);
                return false;
            }
        }

        return false;
    }

    @Override
    public boolean removeCoordinates(final long groupId, final Map<Long, Set<GeographicCoordinates>> matrices) {
        boolean hasChanges = false;
        for (final Map.Entry<Long, Set<GeographicCoordinates>> entry : matrices.entrySet()) {
            for (final GeographicCoordinates coordinate : entry.getValue()) {
                try {
                    hasChanges |= queryExecuter.executeUpdate(
                            queries.get(Query.REMOVE_COORDINATE),
                            DatabaseUtils.makeQueryParams(groupId, entry.getKey(), coordinate.getLatitude(), coordinate.getLongitude())) > 0;
                } catch (SQLException e) {
                    logger.warn("Can't remove coordinate " + coordinate, e);
                }
            }
        }
        return hasChanges;
    }

    @Override
    public boolean updateState(final long groupId, final long matrixId, final GeographicCoordinates coordinate,
                               final boolean isForwardRoute, final int state, final long providerCid) {
        final Map<Integer, Object> params = DatabaseUtils.makeQueryParams(state, providerCid, groupId, matrixId,
                coordinate.getLatitude(), coordinate.getLongitude());

        try {
            final Query query = isForwardRoute ? Query.UPDATE_FORWARD_STATE_FOR_COORDINATE : Query.UPDATE_BACKWARD_STATE_FOR_COORDINATE;
            return queryExecuter.executeUpdate(queries.get(query), params) > 0;
        } catch (SQLException e) {
            logger.warn("Can't update state of coordinate: groupId " + groupId + ",matrixId " + matrixId + ",coordinate " + coordinate, e);
        }

        return false;
    }

    @Override
    public boolean addCoordinates(final long groupId, final Map<Long, Set<GeographicCoordinates>> matrices) {
        boolean hasAdded = false;

        final Map<Long, Set<GeographicCoordinates>> existsMatrices = getExistsCoordinates(groupId);
        for (final Map.Entry<Long, Set<GeographicCoordinates>> entry : matrices.entrySet()) {
            final long matrixId = entry.getKey();
            final Set<GeographicCoordinates> forAdd;
            if (existsMatrices.containsKey(matrixId)) {
                // add only new
                forAdd = new HashSet<GeographicCoordinates>();
                final Set<GeographicCoordinates> existsCoordinates = existsMatrices.get(matrixId);
                for (final GeographicCoordinates coordinate : entry.getValue()) {
                    if (!existsCoordinates.contains(coordinate)) {
                        forAdd.add(coordinate);
                    }
                }
            } else {
                // add all
                forAdd = entry.getValue();
            }
            hasAdded |= insertNewCoordinates(groupId, matrixId, forAdd);
        }

        return hasAdded;
    }

    @Override
    public Pair<Set<DistanceMatrixCoordinate>, Set<DistanceMatrixCoordinate>> getNewAndOldCoordinates(final long groupId, final long matrixId) {

        final List coordinates;
        try {
            coordinates = queryExecuter.getResultList(queries.get(Query.SELECT_EXISTS_COORDINATES_OF_MATRIX_WITH_STATE),
                    DatabaseUtils.makeQueryParams(groupId, matrixId));
        } catch (SQLException e) {
            logger.warn("Can't get coordinates for group " + groupId, e);
            return EMPTY_MATRIX_COORDINATES2;
        }

        final Set<DistanceMatrixCoordinate> newCoordinates = new HashSet<DistanceMatrixCoordinate>();
        final Set<DistanceMatrixCoordinate> oldCoordinates = new HashSet<DistanceMatrixCoordinate>();
        for (final Object item : coordinates) {
            final Object[] objects = (Object[]) item;
            final Double latitude = ((BigDecimal) objects[0]).doubleValue();
            final Double longitude = ((BigDecimal) objects[1]).doubleValue();
            final Integer latitudeInt = (Integer) objects[2];
            final Integer longitudeInt = (Integer) objects[3];
            final Integer forwardState = (Integer) objects[4];
            final Integer backwardState = (Integer) objects[5];
            final boolean calculateForwardState = UpdatingState.SCHEDULED.getValue() == forwardState;
            final boolean calculateBackwardState = UpdatingState.SCHEDULED.getValue() == backwardState;

            final DistanceMatrixCoordinate coordinate = new DistanceMatrixCoordinate(
                    latitude, longitude, latitudeInt, longitudeInt, calculateForwardState, calculateBackwardState);

            if (calculateForwardState || calculateBackwardState) {
                newCoordinates.add(coordinate);
            } else {
                oldCoordinates.add(coordinate);
            }
        }

        return new Pair<Set<DistanceMatrixCoordinate>, Set<DistanceMatrixCoordinate>>(newCoordinates, oldCoordinates);
    }

    @Override
    public boolean synchronizeCoordinates(final long groupId, final List<MatrixCoordinates> matrices) {

        final DistanceMatrixDAOUtils.MergeMatrixResult result = DistanceMatrixDAOUtils.merge(matrices, getExistsCoordinates(groupId));

        if (result.isNoChanges()) {
            return false;
        }

        if (result.isRemoveGroup()) {
            removeGroup(groupId);
            return false;
        }

        if (result.hasRemovedMatrices()) {
            removeMatrices(groupId, result.getRemovedMatrices());
        }

        if (result.hasRemovedCoordinates()) {
            removeCoordinates(groupId, result.getRemovedCoordinates());
        }

        return result.hasAddedCoordinates() && addCoordinates(groupId, result.getAddedCoordinates());
    }

    private static enum Query {
        SELECT_EXISTS_COORDINATES_OF_GROUP,
        SELECT_GROUPS_FOR_UPDATE,
        REMOVE_GROUP,
        REMOVE_MATRICES_IN,
        REMOVE_COORDINATE,
        INSERT_COORDINATE,
        COUNT_GROUP_COORDINATES_WITH_NOT_THIS_CID,
        UPDATE_STATE_FOR_COORDINATES_OF_GROUP,
        SELECT_EXISTS_COORDINATES_OF_MATRIX_WITH_STATE,
        UPDATE_FORWARD_STATE_FOR_COORDINATE,
        UPDATE_BACKWARD_STATE_FOR_COORDINATE,
        SET_PRIORITY,
        COUNT_COORDINATES_FOR_UPDATE,
        RESET_STATES_OF_GROUP
    }
}
